﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using NangLuongTieuDiemDTO.DTO;

namespace NangLuongTieuDiemDAO.DAO
{
    public class SanPhamDAO
    {
        #region "[Insert]"
        /// <summary>
        /// Thêm 1 đối tượng SanPham
        /// </summary>
        /// <param name="spDto">đối tượng SanPham cần thêm</param>
        /// <returns></returns>
        public static int Insert(SanPhamDTO spDto)
        {
            int ID=-1;
            try
            {
                // Mo ket noi
                SqlConnection cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                //Mrchau: sữa lại câu truy vấn. 20.5.2013
                string strSQL = "insert into SanPham(TenSanPham, LoaiSanPhamID, CongSuat, HinhDang, Dai, Rong, Day, Imp, Vmp, Isc, Voc, NguonNhap, Gia, Xoa, GhiChu) values(@TenSanPham , @LoaiSanPhamID , @CongSuat, @HinhDang, @Dai, @Rong, @Day, @Imp, @Vmp, @Isc, @Voc, @NguonNhap, @Gia, @Xoa, @GhiChu)";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);


                cmd.Parameters.Add("@TenSanPham", SqlDbType.NVarChar);
                cmd.Parameters.Add("@LoaiSanPhamID", SqlDbType.Int);
                cmd.Parameters.Add("@CongSuat", SqlDbType.Decimal);
                cmd.Parameters.Add("@HinhDang", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Dai", SqlDbType.Decimal);
                cmd.Parameters.Add("@Rong", SqlDbType.Decimal);
                cmd.Parameters.Add("@Day", SqlDbType.Decimal);
                cmd.Parameters.Add("@Imp", SqlDbType.Decimal);
                cmd.Parameters.Add("@Vmp", SqlDbType.Decimal);
                cmd.Parameters.Add("@Isc", SqlDbType.Decimal);
                cmd.Parameters.Add("@Voc", SqlDbType.Decimal);
                cmd.Parameters.Add("@NguonNhap", SqlDbType.NText);
                cmd.Parameters.Add("@Gia", SqlDbType.Decimal);
                cmd.Parameters.Add("@Xoa", SqlDbType.Bit);
                cmd.Parameters.Add("@GhiChu", SqlDbType.NText);


                cmd.Parameters["@TenSanPham"].Value = spDto.TenSanPham;
                cmd.Parameters["@LoaiSanPhamID"].Value = spDto.LoaiSanPhamID;
                cmd.Parameters["@CongSuat"].Value = Convert.ToDecimal(spDto.CongSuat);
                cmd.Parameters["@HinhDang"].Value = spDto.HinhDang;
                cmd.Parameters["@dai"].Value = Convert.ToDecimal(spDto.Dai);
                cmd.Parameters["@Rong"].Value = Convert.ToDecimal(spDto.Rong);
                cmd.Parameters["@Day"].Value = Convert.ToDecimal(spDto.Day);
                cmd.Parameters["@Imp"].Value = Convert.ToDecimal(spDto.Imp);
                cmd.Parameters["@Vmp"].Value = Convert.ToDecimal(spDto.Vmp);
                cmd.Parameters["@Isc"].Value = Convert.ToDecimal(spDto.Isc);
                cmd.Parameters["@Voc"].Value = Convert.ToDecimal(spDto.Voc);
                cmd.Parameters["@NguonNhap"].Value = spDto.NguonNhap;
                cmd.Parameters["@Gia"].Value = Convert.ToDecimal(spDto.Gia);
                cmd.Parameters["@Xoa"].Value = spDto.Xoa;
                cmd.Parameters["@GhiChu"].Value = spDto.GhiChu;

                cmd.ExecuteNonQuery();

                // lay ID cua dong insert

                strSQL = "Select @@IDENTITY";
                cmd = new SqlCommand(strSQL, cn);
                ID = Convert.ToInt32(cmd.ExecuteScalar());

                // dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Insert", ex);
            }
            return ID;
        }
        #endregion

        #region "[Update]"
        /// <summary>
        /// Cập nhật 1 đối tượng SanPham
        /// </summary>
        /// <param name="spDto">đối tượng SanPham cần cập nhật</param>
        /// <returns></returns>
        public static bool Update(SanPhamDTO spDto)
        {
            bool isSuccess = false;
            SqlConnection cn;
            try
            {
                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl //Cao Hoang An, sua NguoiNhap thành NguonNhap, them Isc bi thieu trong chuoi strSQL
                string strSQL = "Update SanPham Set TenSanPham = @TenSanPham , LoaiSanPhamID = @LoaiSanPhamID , CongSuat = @CongSuat, HinhDang = @HinhDang, Dai = @Dai, Rong = @Rong, Day = @Day, Imp = @Imp, Vmp = @Vmp, Isc = @Isc, Voc = @Voc, NguonNhap = @NguonNhap, Gia = @Gia, Xoa = @Xoa, GhiChu = @GhiChu where ID = @ID and Xoa = 0";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);


                cmd.Parameters.Add("@TenSanPham", SqlDbType.NVarChar);
                cmd.Parameters.Add("@LoaiSanPhamID", SqlDbType.Int);
                cmd.Parameters.Add("@CongSuat", SqlDbType.Decimal);
                cmd.Parameters.Add("@HinhDang", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Dai", SqlDbType.Decimal);
                cmd.Parameters.Add("@Rong", SqlDbType.Decimal);
                cmd.Parameters.Add("@Day", SqlDbType.Decimal);
                cmd.Parameters.Add("@Imp", SqlDbType.Decimal);
                cmd.Parameters.Add("@Vmp", SqlDbType.Decimal);
                cmd.Parameters.Add("@Isc", SqlDbType.Decimal);
                cmd.Parameters.Add("@Voc", SqlDbType.Decimal);
                cmd.Parameters.Add("@NguonNhap", SqlDbType.NText);
                cmd.Parameters.Add("@Gia", SqlDbType.Decimal);
                cmd.Parameters.Add("@Xoa", SqlDbType.Bit);
                cmd.Parameters.Add("@GhiChu", SqlDbType.NText);
                cmd.Parameters.Add("@ID", SqlDbType.Int);


                cmd.Parameters["@TenSanPham"].Value    = spDto.TenSanPham;
                cmd.Parameters["@LoaiSanPhamID"].Value = spDto.LoaiSanPhamID;
                cmd.Parameters["@CongSuat"].Value      = Convert.ToDecimal(spDto.CongSuat);
                cmd.Parameters["@HinhDang"].Value      = spDto.HinhDang;
                cmd.Parameters["@dai"].Value           = Convert.ToDecimal(spDto.Dai);
                cmd.Parameters["@Rong"].Value          = Convert.ToDecimal(spDto.Rong);
                cmd.Parameters["@Day"].Value           = Convert.ToDecimal(spDto.Day);
                cmd.Parameters["@Imp"].Value           = Convert.ToDecimal(spDto.Imp);
                cmd.Parameters["@Vmp"].Value           = Convert.ToDecimal(spDto.Vmp);
                cmd.Parameters["@Isc"].Value           = Convert.ToDecimal(spDto.Isc);
                cmd.Parameters["@Voc"].Value           = Convert.ToDecimal(spDto.Voc);
                cmd.Parameters["@NguonNhap"].Value     = spDto.NguonNhap;
                cmd.Parameters["@Gia"].Value           = Convert.ToDecimal(spDto.Gia);
                cmd.Parameters["@Xoa"].Value           = spDto.Xoa;
                cmd.Parameters["@GhiChu"].Value        = spDto.GhiChu;
                cmd.Parameters["@ID"].Value            = spDto.ID;

                cmd.ExecuteNonQuery();

                // dong ket noi csdl
                cn.Close();
                isSuccess= true;

            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Update", ex);
            }
            return isSuccess;
        }
        #endregion

        #region "[Delete]"
        /// <summary>
        /// Xóa 1 đối tượng SanPham
        /// </summary>
        /// <param name="ID">mã đối tượng SanPham cần xóa</param>
        /// <returns></returns>
        public static bool Delete(int ID)
        {
            bool isSuccess = false;
            SqlConnection cn;
            try
            {
                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Update SanPham Set Xoa = 1 where ID = @ID";


                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters["@ID"].Value = ID;


                cmd.ExecuteNonQuery();
                // dong ket noi csdl
                cn.Close();
                isSuccess = true;

            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Delete", ex);
            }
            return isSuccess;
        }
        #endregion

        #region "[Get all]"
        /// <summary>
        /// Lấy danh sách các đối tượng SanPham
        /// </summary>
        /// <returns></returns>
        public static List<SanPhamDTO> GetAll()
        {
            List<SanPhamDTO> ds_spDto = new List<SanPhamDTO>();
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Select * From SanPham where Xoa = 0";


                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                SqlDataReader dr = cmd.ExecuteReader();


                // Cao Hoang An, tranh truong hop CSDL NULL minh select khong duoc, minh can sua doan code nay phuc vu cho viec tim kiem san pham
                while (dr.Read() == true)
                {
                    SanPhamDTO spDto = new SanPhamDTO();
                    spDto.ID = dr.GetInt32(0);
                    if (dr[1] != DBNull.Value)
                    {
                        spDto.TenSanPham = dr.GetString(1);
                    }
                    spDto.LoaiSanPhamID = dr.GetInt32(2);
                    if (dr[3] != DBNull.Value)
                    {
                        spDto.CongSuat = Convert.ToDouble(dr.GetDecimal(3));
                    }
                    if (dr[4] != DBNull.Value)
                    {
                        spDto.HinhDang = dr.GetString(4);
                    }
                    if (dr[5] != DBNull.Value)
                    {
                        spDto.Dai = Convert.ToDouble(dr.GetDecimal(5));
                    }
                    if (dr[6] != DBNull.Value)
                    {
                        spDto.Rong = Convert.ToDouble(dr.GetDecimal(6));
                    }
                    if (dr[7] != DBNull.Value)
                    {
                        spDto.Day = Convert.ToDouble(dr.GetDecimal(7));
                    }
                    if (dr[8] != DBNull.Value)
                    {
                        spDto.Imp = Convert.ToDouble(dr.GetDecimal(8));
                    }
                    if (dr[9] != DBNull.Value)
                    {
                        spDto.Vmp = Convert.ToDouble(dr.GetDecimal(9));
                    }
                    if (dr[10] != DBNull.Value)
                    {
                        spDto.Isc = Convert.ToDouble(dr.GetDecimal(10));
                    }
                    if (dr[11] != DBNull.Value)
                    {
                        spDto.Voc = Convert.ToDouble(dr.GetDecimal(11));
                    }
                    if (dr[12] != DBNull.Value)
                    {
                        spDto.NguonNhap = dr.GetString(12);
                    }
                    if (dr[13] != DBNull.Value)
                    {
                        spDto.Gia = Convert.ToDouble(dr.GetDecimal(13));
                    }
                    if (dr[14] != DBNull.Value)
                    {
                        spDto.Xoa = dr.GetBoolean(14);
                    }
                    if (dr[15] != DBNull.Value)
                    {
                        spDto.GhiChu = dr.GetString(15);
                    }
                    ds_spDto.Add(spDto);
                }

                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("GetAll", ex);
            }
            return ds_spDto;
        }
        #endregion

        #region "[SelectByPrimaryKey]"
        /// <summary>
        /// Lấy 1 đối tượng SanPham
        /// </summary>
        /// <param name="ID">mã đối tượng SanPham</param>
        /// <returns></returns>
        public static SanPhamDTO SelectByPrimaryKey(int ID)
        {
            SanPhamDTO spDto = null;
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Select * From SanPham where ID = @ID and Xoa = 0";


                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters["@ID"].Value = ID;

                SqlDataReader dr = cmd.ExecuteReader();
                spDto = new SanPhamDTO();

                while (dr.Read() == true)
                {
                    spDto.ID = dr.GetInt32(0);
                    spDto.TenSanPham = dr.GetString(1);
                    spDto.LoaiSanPhamID = dr.GetInt32(2);
                    spDto.CongSuat = Convert.ToDouble(dr.GetDecimal(3));
                    spDto.HinhDang = dr.GetString(4);
                    spDto.Dai = Convert.ToDouble(dr.GetDecimal(5));
                    spDto.Rong = Convert.ToDouble(dr.GetDecimal(6));
                    spDto.Day = Convert.ToDouble(dr.GetDecimal(7));
                    spDto.Imp = Convert.ToDouble(dr.GetDecimal(8));
                    spDto.Vmp = Convert.ToDouble(dr.GetDecimal(9));
                    spDto.Isc = Convert.ToDouble(dr.GetDecimal(10));
                    spDto.Voc = Convert.ToDouble(dr.GetDecimal(11));
                    spDto.NguonNhap = dr.GetString(12);
                    spDto.Gia = Convert.ToDouble(dr.GetDecimal(13));
                    spDto.Xoa = dr.GetBoolean(14);
                    spDto.GhiChu = dr.GetString(15);
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("SelectByPrimaryKey", ex);
            }
            return spDto;
        }
        #endregion

        #region "[SelectByMulti]"//Cao Hoang An - 1241301
        //Lấy danh sách các đối tượng SanPham trên nhiêu tiêu chi
        public static List<SanPhamDTO> SelectByMulti(bool flagNumberic,object Data,int LoaiSanPhamChinhID)
        {
            List<SanPhamDTO> ds_spDto = new List<SanPhamDTO>();
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl

                string strSQL = " Select * From SanPham s, LoaiSanPham l where s.Xoa = 0 and l.LoaiSanPhamChinhID = @LoaiSanPhamChinhID and l.ID = s.LoaiSanPhamID ";

                if (flagNumberic == true)
                {
                    strSQL += "and (s.CongSuat = @CongSuat or s.Dai = @Dai or s.Rong = @Rong or s.Day = @Day or s.Imp = @Imp or s.Vmp = @Vmp or s.Isc = @Isc or s.Voc = @Voc or s.Gia = @Gia) ";
                }
                else
                {
                    strSQL += "and (s.TenSanPham like @TenSanPham or s.HinhDang like @HinhDang or s.NguonNhap like @NguonNhap) ";
                }

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                if (flagNumberic == true)
                {
                    cmd.Parameters.Add("@LoaiSanPhamChinhID", SqlDbType.Int);
                    cmd.Parameters["@LoaiSanPhamChinhID"].Value = LoaiSanPhamChinhID;
                    cmd.Parameters.Add("@CongSuat", SqlDbType.Decimal);
                    cmd.Parameters["@CongSuat"].Value = Data;
                    cmd.Parameters.Add("@Dai", SqlDbType.Decimal);
                    cmd.Parameters["@Dai"].Value = Data;
                    cmd.Parameters.Add("@Rong", SqlDbType.Decimal);
                    cmd.Parameters["@Rong"].Value = Data;
                    cmd.Parameters.Add("@Day", SqlDbType.Decimal);
                    cmd.Parameters["@Day"].Value = Data;
                    cmd.Parameters.Add("@Imp", SqlDbType.Decimal);
                    cmd.Parameters["@Imp"].Value = Data;
                    cmd.Parameters.Add("@Vmp", SqlDbType.Decimal);
                    cmd.Parameters["@Vmp"].Value = Data;
                    cmd.Parameters.Add("@Isc", SqlDbType.Decimal);
                    cmd.Parameters["@Isc"].Value = Data;
                    cmd.Parameters.Add("@Voc", SqlDbType.Decimal);
                    cmd.Parameters["@Voc"].Value = Data;
                    cmd.Parameters.Add("@Gia", SqlDbType.Decimal);
                    cmd.Parameters["@Gia"].Value = Data;
                }
                else
                {
                    cmd.Parameters.Add("@LoaiSanPhamChinhID", SqlDbType.Int);
                    cmd.Parameters["@LoaiSanPhamChinhID"].Value = LoaiSanPhamChinhID;
                    cmd.Parameters.Add("@TenSanPham", SqlDbType.NVarChar);
                    cmd.Parameters["@TenSanPham"].Value = "%" + Data + "%";
                    cmd.Parameters.Add("@HinhDang", SqlDbType.NVarChar);
                    cmd.Parameters["@HinhDang"].Value = "%" + Data + "%";
                    cmd.Parameters.Add("@NguonNhap", SqlDbType.NVarChar);
                    cmd.Parameters["@NguonNhap"].Value = "%" + Data + "%";
                }

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read() == true)
                {
                    SanPhamDTO spDto = new SanPhamDTO();
                    if (dr[0] != DBNull.Value)
                    {
                        spDto.ID = dr.GetInt32(0);
                    }
                    if (dr[1] != DBNull.Value)
                    {
                        spDto.TenSanPham = dr.GetString(1);
                    }
                    if (dr[2] != DBNull.Value)
                    {
                        spDto.LoaiSanPhamID = dr.GetInt32(2);
                    }
                    if (dr[3] != DBNull.Value)
                    {
                        spDto.CongSuat = Convert.ToDouble(dr.GetDecimal(3));
                    }
                    if (dr[4] != DBNull.Value)
                    {
                        spDto.HinhDang = dr.GetString(4);
                    }
                    if (dr[5] != DBNull.Value)
                    {
                        spDto.Dai = Convert.ToDouble(dr.GetDecimal(5));
                    }
                    if (dr[6] != DBNull.Value)
                    {
                        spDto.Rong = Convert.ToDouble(dr.GetDecimal(6));
                    }
                    if (dr[7] != DBNull.Value)
                    {
                        spDto.Day = Convert.ToDouble(dr.GetDecimal(7));
                    }
                    if (dr[8] != DBNull.Value)
                    {
                        spDto.Imp = Convert.ToDouble(dr.GetDecimal(8));
                    }
                    if (dr[9] != DBNull.Value)
                    {
                        spDto.Vmp = Convert.ToDouble(dr.GetDecimal(9));
                    }
                    if (dr[10] != DBNull.Value)
                    {
                        spDto.Isc = Convert.ToDouble(dr.GetDecimal(10));
                    }
                    if (dr[11] != DBNull.Value)
                    {
                        spDto.Voc = Convert.ToDouble(dr.GetDecimal(11));
                    }
                    if (dr[12] != DBNull.Value)
                    {
                        spDto.NguonNhap = dr.GetString(12);
                    }
                    if (dr[13] != DBNull.Value)
                    {
                        spDto.Gia = Convert.ToDouble(dr.GetDecimal(13));
                    }
                    if (dr[14] != DBNull.Value)
                    {
                        spDto.Xoa = dr.GetBoolean(14);
                    }
                    if (dr[15] != DBNull.Value)
                    {
                        spDto.GhiChu = dr.GetString(15);
                    }
                    ds_spDto.Add(spDto);
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("SelectByMulti", ex);
            }
            return ds_spDto;
        }
        #endregion

        #region "[SelectByLoaiSanPhamID]"// Cao Hoang An - 1241301
        //TimKiem san pham theo LoaiSanPham
        public static List<SanPhamDTO> SelectByLoaiSanPhamID(int LoaiSanPhamID)
        {
            List<SanPhamDTO> ds_spDto = new List<SanPhamDTO>();
            SanPhamDTO spDto;
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Select * From SanPham where Xoa = 0 and LoaiSanPhamID = @LoaiSanPhamID";


                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@LoaiSanPhamID", SqlDbType.Int);
                cmd.Parameters["@LoaiSanPhamID"].Value = LoaiSanPhamID;

                SqlDataReader dr = cmd.ExecuteReader();
                spDto = new SanPhamDTO();

                while (dr.Read() == true)
                {
                    spDto = new SanPhamDTO();
                    spDto.ID = dr.GetInt32(0);
                    if (dr[1] != DBNull.Value)
                    {
                        spDto.TenSanPham = dr.GetString(1);
                    }
                    spDto.LoaiSanPhamID = dr.GetInt32(2);
                    if (dr[3] != DBNull.Value)
                    {
                        spDto.CongSuat = Convert.ToDouble(dr.GetDecimal(3));
                    }
                    if (dr[4] != DBNull.Value)
                    {
                        spDto.HinhDang = dr.GetString(4);
                    }
                    if (dr[5] != DBNull.Value)
                    {
                        spDto.Dai = Convert.ToDouble(dr.GetDecimal(5));
                    }
                    if (dr[6] != DBNull.Value)
                    {
                        spDto.Rong = Convert.ToDouble(dr.GetDecimal(6));
                    }
                    if (dr[7] != DBNull.Value)
                    {
                        spDto.Day = Convert.ToDouble(dr.GetDecimal(7));
                    }
                    if (dr[8] != DBNull.Value)
                    {
                        spDto.Imp = Convert.ToDouble(dr.GetDecimal(8));
                    }
                    if (dr[9] != DBNull.Value)
                    {
                        spDto.Vmp = Convert.ToDouble(dr.GetDecimal(9));
                    }
                    if (dr[10] != DBNull.Value)
                    {
                        spDto.Isc = Convert.ToDouble(dr.GetDecimal(10));
                    }
                    if (dr[11] != DBNull.Value)
                    {
                        spDto.Voc = Convert.ToDouble(dr.GetDecimal(11));
                    }
                    if (dr[12] != DBNull.Value)
                    {
                        spDto.NguonNhap = dr.GetString(12);
                    }
                    if (dr[13] != DBNull.Value)
                    {
                        spDto.Gia = Convert.ToDouble(dr.GetDecimal(13));
                    }
                    if (dr[14] != DBNull.Value)
                    {
                        spDto.Xoa = dr.GetBoolean(14);
                    }
                    if (dr[15] != DBNull.Value)
                    {
                        spDto.GhiChu = dr.GetString(15);
                    }
                    ds_spDto.Add(spDto);
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("SelectByLoaiSanPhamID", ex);
            }
            return ds_spDto;
        }
        #endregion

        #region "[tim kiem tư vấn pin năng lượng]"  //tran ha nguyen thang

        public static List<SanPhamDTO> TimKiemTuVanPinNangLuong(double congsuat, int ID1, int ID2, int ID3)
        {
            List<SanPhamDTO> ds_spDto = new List<SanPhamDTO>();
            SanPhamDTO spDto;
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Select * From SanPham where CongSuat <= @CongSuat and (LoaiSanPhamID = @ID1 or LoaiSanPhamID = @ID2 or LoaiSanPhamID = @ID3) and Xoa = 0";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@CongSuat", SqlDbType.Decimal);
                cmd.Parameters.Add("@ID1", SqlDbType.Int);
                cmd.Parameters.Add("@ID2", SqlDbType.Int);
                cmd.Parameters.Add("@ID3", SqlDbType.Int);

                cmd.Parameters["@CongSuat"].Value = Convert.ToDecimal(congsuat);
                cmd.Parameters["@ID1"].Value = ID1;
                cmd.Parameters["@ID2"].Value = ID2;
                cmd.Parameters["@ID3"].Value = ID3;

             

                SqlDataReader dr = cmd.ExecuteReader();
                spDto = new SanPhamDTO();

                while (dr.Read() == true)
                {
                    spDto = new SanPhamDTO();
                    spDto.ID = dr.GetInt32(0);
                    if (dr[1] != DBNull.Value)
                    {
                        spDto.TenSanPham = dr.GetString(1);
                    }
                    spDto.LoaiSanPhamID = dr.GetInt32(2);
                    if (dr[3] != DBNull.Value)
                    {
                        spDto.CongSuat = Convert.ToDouble(dr.GetDecimal(3));
                    }
                    if (dr[4] != DBNull.Value)
                    {
                        spDto.HinhDang = dr.GetString(4);
                    }
                    if (dr[5] != DBNull.Value)
                    {
                        spDto.Dai = Convert.ToDouble(dr.GetDecimal(5));
                    }
                    if (dr[6] != DBNull.Value)
                    {
                        spDto.Rong = Convert.ToDouble(dr.GetDecimal(6));
                    }
                    if (dr[7] != DBNull.Value)
                    {
                        spDto.Day = Convert.ToDouble(dr.GetDecimal(7));
                    }
                    if (dr[8] != DBNull.Value)
                    {
                        spDto.Imp = Convert.ToDouble(dr.GetDecimal(8));
                    }
                    if (dr[9] != DBNull.Value)
                    {
                        spDto.Vmp = Convert.ToDouble(dr.GetDecimal(9));
                    }
                    if (dr[10] != DBNull.Value)
                    {
                        spDto.Isc = Convert.ToDouble(dr.GetDecimal(10));
                    }
                    if (dr[11] != DBNull.Value)
                    {
                        spDto.Voc = Convert.ToDouble(dr.GetDecimal(11));
                    }
                    if (dr[12] != DBNull.Value)
                    {
                        spDto.NguonNhap = dr.GetString(12);
                    }
                    if (dr[13] != DBNull.Value)
                    {
                        spDto.Gia = Convert.ToDouble(dr.GetDecimal(13));
                    }
                    if (dr[14] != DBNull.Value)
                    {
                        spDto.Xoa = dr.GetBoolean(14);
                    }
                    if (dr[15] != DBNull.Value)
                    {
                        spDto.GhiChu = dr.GetString(15);
                    }
                    ds_spDto.Add(spDto);
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("SelectByLoaiSanPhamID", ex);
            }
            return ds_spDto;
         
        }
        #endregion



        #region "[tim kiem tư vấn bộ điều khiển xạc]"  //tran ha nguyen thang

        public static List<SanPhamDTO> TimKiemTuVanDieuKhienXac(double Isc_max)
        {
            List<SanPhamDTO> ds_spDto = new List<SanPhamDTO>();
            SanPhamDTO spDto;
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "select TenSanPham, SP.LoaiSanPhamID, Gia, SP.GhiChu, Imp, Voc, Vmp, NguonNhap" ;
                strSQL = strSQL + " from SanPham SP, LoaiSanPham LSP, LoaiSanPhamChinh LSPC"  ;
                strSQL = strSQL + " where LSPC.ID = 3 and LSPC.ID = LSP.LoaiSanPhamChinhID and SP.LoaiSanPhamID = LSP.ID and Imp >= @Isc_max";
                   
                
                

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@Isc_max", SqlDbType.Decimal);
                cmd.Parameters["@Isc_max"].Value = Isc_max;



                SqlDataReader dr = cmd.ExecuteReader();
                spDto = new SanPhamDTO();

                while (dr.Read() == true)
                {

                    spDto = new SanPhamDTO();
                    
                    // ten sanpham
                    if (dr[0] != DBNull.Value)
                    {
                        spDto.TenSanPham = dr.GetString(0);
                    }

                  
                    // loaisanpham
                    spDto.LoaiSanPhamID = dr.GetInt32(1);

                    // gia
                    if (dr[2] != DBNull.Value)
                    {
                        spDto.Gia = Convert.ToDouble(dr.GetDecimal(2));
                    }

                    // ghi chu
                    if (dr[3] != DBNull.Value)
                    {
                        spDto.GhiChu = dr.GetString(3);
                    }

                    //Imp (cuong do dong dien sinh ra o muc thap nhat)
                    if (dr[4] != DBNull.Value)
                    {
                        spDto.Imp = Convert.ToDouble(dr.GetDecimal(4));
                    }

                    //Voc (hieu dien the Max)
                    if (dr[5] != DBNull.Value)
                    {
                        spDto.Voc = Convert.ToDouble(dr.GetDecimal(5));
                    }

                    // Vmp (hieu dien the Min)
                    if (dr[6] != DBNull.Value)
                    {
                        spDto.Vmp = Convert.ToDouble(dr.GetDecimal(6));
                    }

                    // Nguon nhap

                    if (dr[7] != DBNull.Value)
                    {
                        spDto.NguonNhap = dr.GetString(7);
                    }
                   
                    ds_spDto.Add(spDto);
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("SelectByLoaiSanPhamID", ex);
            }
            return ds_spDto;

        }
        #endregion

        #region "[Hàm insert lỗi hệ thống]"
        public static void InSert_LoiHeThong(string TenHam, Exception ex)
        {
            LoiHeThongDTO lhtDto = new LoiHeThongDTO();
            lhtDto.TenLoi = ex.Message.ToString();
            lhtDto.NgayTao = DateTime.Now;
            lhtDto.TenHamBiLoi = "SanPhamDAO_" + TenHam;
            lhtDto.SuaLoi = false;
            lhtDto.GhiChu = "";
            LoiHeThongDAO.Insert(lhtDto);

        }
        #endregion
    }
}
